﻿Imports DTO
Imports System.Data.SqlClient
Public Class KhachHangDAO

    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From KHACHHANG"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function

    Public Function LayDanhSach() As List(Of KhachHangDTO)
        Dim ds As New List(Of KhachHangDTO)
        Dim cn As SqlConnection
        Dim strSQL As String
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        strSQL = "Select * From KHACHHANG"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        Dim khạchhang As New KhachHangDTO()
        While (dr.Read())
            khạchhang = New KhachHangDTO()
            khạchhang.MaKhachHang = dr("MaKhachHang")
            khạchhang.TenKhachHang = dr("TenKhachHang")
            ds.Add(khạchhang)
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return ds
    End Function

    'thêm
    Public Sub Them(ByVal khdto As KhachHangDTO)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into KHACHHANG(TenKhachHang,SoTienNo,DiaChi,DienThoai,Email,SoTienThu) values(@TenKhachHang,@SoTienNo,@DiaChi,@DienThoai,@Email,@SoTienThu)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)

        cmd.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar)
        cmd.Parameters("@TenKhachHang").Value = khdto.TenKhachHang

        cmd.Parameters.Add("@SoTienNo", SqlDbType.Int)
        cmd.Parameters("@SoTienNo").Value = khdto.SoTienNo

        cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar)
        cmd.Parameters("@DiaChi").Value = khdto.DiaChi

        cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
        cmd.Parameters("@DienThoai").Value = khdto.DienThoai

        cmd.Parameters.Add("@Email", SqlDbType.Int)
        cmd.Parameters("@Email").Value = khdto.Email

        cmd.Parameters.Add("@SoTienThu", SqlDbType.Int)
        cmd.Parameters("@SoTienThu").Value = khdto.SoTienThu


        cmd.ExecuteNonQuery()


        strSQL = "Select @@IDENTITY"
        cmd = New SqlCommand(strSQL, cn)
        khdto.MaKhachHang = Convert.ToInt32(cmd.ExecuteScalar())

        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

End Class
